-----갈라리-----------------------------------------------



drop table gallery_reply;
create table gallery_reply(
reply_no number primary key,
w_date date not null,
content varchar2(1000) not null,
restep number not null,
m_uid number not null,
gallery_no number not null,
constraint fk_gallery_reply_id foreign key(m_uid) references member(m_uid) on delete cascade,
foreign key(gallery_no) references gallery(gallery_no) on delete cascade
)

drop sequence gallery_reply_seq;
create sequence gallery_reply_seq nocache;



drop table wow
create table wow(
	m_uid number not null,
	gallery_no number not null,
	constraint fk_wow_id foreign key(m_uid) references member(m_uid) on delete cascade,
						 foreign key(gallery_no) references gallery(gallery_no) on delete cascade,
						 unique(m_uid,gallery_no)
)


drop table gallery;

create table gallery(
	gallery_no number primary key,
	title varchar2(100), 
	content varchar2(2000), 
	category varchar2(20) not null, 
	wow number not null,	
	w_date date not null,
	count number not null, 
	org_imgname varchar2(200) not null,
	thumb_imgname varchar2(250) not null, 
	m_uid number ,
	best_pri_week number,
	constraint fk_gallery_id foreign key(m_uid) references member(m_uid) on delete set null,
							 foreign key(best_pri_week) references best_weekly(best_pri_week) on delete set null
);



drop sequence gallery_seq;
create sequence gallery_seq nocache;


drop table best_weekly; 

create table best_weekly(
	best_pri_week number primary key, 
	year number not null, 
	month number not null, 
	week number not null,
	s_day varchar2(20) not null,
	l_day varchar2(20) not null
);

drop sequence best_weekly_seq;
create sequence best_weekly_seq nocache;

select * from gallery

-----보드-------------------------------------------------
drop table board_reply;
create table board_reply(
reply_no number primary key,
w_date date not null,
content varchar2(1000) not null,
restep number not null,
m_uid number not null,
board_no number not null,
constraint fk_board_reply_id foreign key(m_uid) references member(m_uid) on delete cascade,
foreign key(board_no) references board(board_no) on delete cascade
)

drop sequence reply_seq;
create sequence reply_seq nocache;

drop table board;
create table board(
	board_no number primary key,
	title varchar2(100) not null,
	content varchar2(2000) not null,
	w_date date not null,
	count number not null,
	category varchar2(20) not null,
	m_uid number not null,
	orgfilename varchar2(500),
	newfilename varchar2(500),
	constraint fk_board_id foreign key(m_uid) references member(m_uid) on delete cascade
);

drop sequence board_seq;
create sequence board_seq nocache;

-----쪽지-------------------------------------------------

drop table send_message;
drop table receive_message;

create table send_message(
	message_no number primary key,
	title varchar2(50) not null,
	content varchar2(1000) not null,
	w_date date not null,
	to_uid number ,
	from_uid number ,--닉네임으로교체
	constraint fk_send_toid foreign key(to_uid) references member(m_uid) on delete set null,
	constraint fk_send_fromid foreign key(from_uid) references member(m_uid) on delete set null
);

create table receive_message(
	message_no number primary key,
	title varchar2(50) not null,
	content varchar2(1000) not null,
	w_date date not null,
	to_uid number ,--닉네임으로교체
	from_uid number ,
	constraint fk_receive_toid foreign key(to_uid) references member(m_uid) on delete set null,
	constraint fk_receive_fromid foreign key(from_uid) references member(m_uid) on delete set null
);

drop sequence send_message_seq;
drop sequence receive_message_seq;

create sequence send_message_seq;
create sequence receive_message_seq;

-----푸렌-------------------------------------------------
drop table friend;
create table friend(
	m_uid number not null,
	friend_uid number not null,
	send_req number not null,
	accept_req number not null,
	constraint fk_sns_user_id foreign key(m_uid) references member(m_uid) on delete cascade,
	constraint fk_sns_friend_id foreign key(friend_uid) references member(m_uid) on delete cascade
);

-----멤버-------------------------------------------------
drop table member;
create table member(
	m_uid number primary key,
	m_id varchar2(100) not null,
	m_password varchar2(50) not null,
	m_nickname varchar2(30) not null,
	m_level number not null,
	m_info varchar2(200) not null,
	m_pro_imgname varchar2(300) not null
);

insert into member(m_uid,m_id,m_password,m_nickname,m_level,m_info,m_pro_imgname)
values(0,'admin@artisan.com','1234','탈퇴한유저',99,' ','default');

drop sequence member_seq;
create sequence member_seq nocache;

----------------------------insert-----------------------------------

insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,1,1,20120101,20120107);
insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,1,2,20120108,20120114);
insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,1,3,20120115,20120121);
insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,1,4,20120122,20120128);
insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,1,5,20120129,20120204);

insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,2,1,20120205,20120211);
insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,2,2,20120212,20120218);
insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,2,3,20120219,20120225);
insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,2,4,20120215,20120303);

insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,3,1,20120304,20120310);
insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,3,2,20120310,20120317);
insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,3,3,20120318,20120324);
insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,3,4,20120325,20120331);

insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,4,1,20120401,20120407);
insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,4,2,20120408,20120414);
insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,4,3,20120415,20120421);
insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,4,4,20120422,20120428);
insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,4,5,20120429,20120505);

insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,5,1,20120506,20120512);
insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,5,2,20120513,20120519);
insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,5,3,20120520,20120526);
insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,5,4,20120527,20120602);

insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,6,1,20120603,20120609);
insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,6,2,20120610,20120616);
insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,6,3,20120617,20120623);
insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,6,4,20120624,20120630);

insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,7,1,20120701,20120707);
insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,7,2,20120708,20120714);
insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,7,3,20120715,20120721);
insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,7,4,20120722,20120728);
insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,7,5,20120729,20120804);

insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,8,1,20120805,20120811);
insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,8,2,20120812,20120818);
insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,8,3,20120819,20120825);
insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,8,4,20120826,20120901);

insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,9,1,20120902,20120908);
insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,9,2,20120909,20120915);
insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,9,3,20120916,20120922);
insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,9,4,20120923,20120929);
insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,9,5,20120930,20121006);

insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,10,1,20121007,20121013);
insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,10,2,20121014,20121020);
insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,10,3,20121021,20121027);
insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,10,4,20121028,20121103);

insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,11,1,20121104,20121110);
insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,11,2,20121111,20121117);
insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,11,3,20121118,20121124);
insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,11,4,20121125,20121201);

insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,12,1,20121202,20121208);
insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,12,2,20121209,20121215);
insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,12,3,20121216,20121222);
insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,12,4,20121223,20121229);
insert into best_weekly (best_pri_week,year,month,week,s_day,l_day) values(best_weekly_seq.nextVal,2012,12,5,20121230,20130105);

select * from gallery
select * from gallery
where best_pri_week=52 and category='art'